const { dbQuery, dbTableName } = require("../db")

const accountGetAll = async (limit) => {
    // 获取总数
    const total = await dbQuery(`
        select 
            count(*) as total
        from 
            ${dbTableName.accountTable} a,
            ${dbTableName.categoryTable} c
        where 
            a.category_id=c.category_id
    `)

    return dbQuery(`
        select 
            a.*, 
            c.category_name 
        from 
            ${dbTableName.accountTable} a,
            ${dbTableName.categoryTable} c
        where 
            a.category_id=c.category_id
        ORDER BY a.date_time desc
        limit ${(limit - 1) * 8},8
    `)
    .then(data => {
        return {
            accounts: data,
            total: total[0].total
        }
    });
}

const accountGet = async (date_time, type) => {
    let dateFormat = "date_time,'%Y-%m'";
    let valueDate = new Date(date_time);
    if (type === 'year') {
        dateFormat = "date_time,'%Y'";
        valueDate = valueDate.getFullYear();
    } else if (type === 'month') {
        let month = valueDate.getMonth() + 1;
        if (month < 10) month = `0${month}`;
        valueDate = `${valueDate.getFullYear()}-${month}`;
    }
    // 获取总数
    const total = await dbQuery(`
        select 
            count(*) as total
        from 
            ${dbTableName.accountTable} 
        where 
            DATE_FORMAT(${dateFormat}) = '${valueDate}' 
    `)
    // console.log(valueDate, dateFormat)
    return await dbQuery(`
        select 
            a.*, 
            c.category_name 
        from 
            ${dbTableName.accountTable} a,
            ${dbTableName.categoryTable} c
        where 
            DATE_FORMAT(${dateFormat}) = '${valueDate}' 
            and a.category_id=c.category_id
        ORDER BY a.date_time asc
    `)
    .then(data => {
        return {
            accounts: data,
            total: total[0].total
        }
    });
}

const accountAdd = async (account_type, category_id, amount, date_time, notes) => {
    return await dbQuery(
        `insert into 
            ${dbTableName.accountTable}
        (
            account_type,
            category_id,
            amount,
            date_time,
            notes
        ) 
        values 
        (
            '${account_type}',
            ${category_id},
            ${amount},
            '${new Date(date_time).toLocaleDateString().split('/').join('-')}',
            '${notes}'
        )`
    )
}

const accountDelete = async (account_id) => {
    return await dbQuery(`delete from ${dbTableName.accountTable} where account_id='${account_id}'`)
}

const accountUpdate = async (account_id, account_type, category_id, amount, date_time, notes) => {
    // console.log(date_time)
 
    return await dbQuery(`
        update 
            ${dbTableName.accountTable} 
        set 
            account_type='${account_type}',
            category_id=${category_id},
            amount=${amount},
            date_time='${new Date(date_time).toLocaleDateString().split('/').join('-')}', 
            notes='${notes}'
        where 
            account_id='${account_id}'
    `)
}

const accountFilter = async (date_time, category_id, limit) => {
    let dateFormat = "date_time,'%Y-%m'";
    const queryDate = new Date(date_time);
    let month = queryDate.getMonth() + 1;
    let valueDate = `${queryDate.getFullYear()}-${month < 10 ? `0${month}` : month}`;
    // 获取总数
    const total = await dbQuery(`
        select 
            count(*) as total
        from 
            ${dbTableName.accountTable} 
        ${date_time || category_id ? 'where' : ''}
            ${date_time ? `DATE_FORMAT(${dateFormat}) = '${valueDate}'` : ''}
            ${(date_time && category_id) ? 'and' : '' }
            ${category_id ? `category_id='${category_id}'` : ''}
    `)
    // console.log(total)
    // console.log(`
    //     select 
    //         a.*, 
    //         c.category_name 
    //     from 
    //         ${dbTableName.accountTable} a,
    //         ${dbTableName.categoryTable} c
    //     where 
    //         ${date ? `DATE_FORMAT(${dateFormat}) = '${valueDate}'` : ''}
    //         ${(date && category_id) ? 'and' : '' }
    //         ${category_id ? `a.category_id='${category_id}'` : ''}
    //         ${(date || category_id) ? 'and' : '' }
    //         a.category_id=c.category_id
    //     ORDER BY a.date_time desc
    // `)
    return await dbQuery(`
        select 
            a.*, 
            c.category_name 
        from 
            ${dbTableName.accountTable} a,
            ${dbTableName.categoryTable} c
        where 
            ${date_time ? `DATE_FORMAT(${dateFormat}) = '${valueDate}'` : ''}
            ${(date_time && category_id) ? 'and' : '' }
            ${category_id ? `a.category_id='${category_id}'` : ''}
            ${(date_time || category_id) ? 'and' : '' }
            a.category_id=c.category_id
        ORDER BY a.date_time desc
        limit ${(limit - 1) * 8}, 8
    `)
    .then(data => {
        return {
            accounts: data,
            total: total[0].total
        }
    });
}

module.exports = {
    accountGetAll,
    accountGet,
    accountAdd,
    accountDelete,
    accountUpdate,
    accountFilter
}

